-- 动物识别专家系统数据库脚本
-- 此脚本用于创建数据库、表结构并导入初始数据

-- 创建数据库
IF NOT EXISTS (SELECT * FROM sys.databases WHERE name = 'AnimalExpertSystem')
BEGIN
    CREATE DATABASE AnimalExpertSystem;
    PRINT '数据库 AnimalExpertSystem 创建成功';
END
ELSE
BEGIN
    PRINT '数据库 AnimalExpertSystem 已存在';
END
GO

-- 使用数据库
USE AnimalExpertSystem;
GO

-- 创建特征表
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Features]') AND type in (N'U'))
BEGIN
    CREATE TABLE [dbo].[Features] (
        [id] INT IDENTITY(1,1) PRIMARY KEY,
        [name] NVARCHAR(255) NOT NULL UNIQUE
    );
    PRINT '特征表 Features 创建成功';
END
ELSE
BEGIN
    PRINT '特征表 Features 已存在';
END
GO

-- 创建规则表
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Rules]') AND type in (N'U'))
BEGIN
    CREATE TABLE [dbo].[Rules] (
        [id] INT IDENTITY(1,1) PRIMARY KEY,
        [name] NVARCHAR(255) NOT NULL,
        [category] NVARCHAR(255) NOT NULL,
        [conditions] NVARCHAR(MAX) NOT NULL,
        [conclusion] NVARCHAR(255) NOT NULL
    );
    PRINT '规则表 Rules 创建成功';
END
ELSE
BEGIN
    PRINT '规则表 Rules 已存在';
END
GO

-- 清空现有数据（如果需要）
-- TRUNCATE TABLE Features;
-- TRUNCATE TABLE Rules;
-- PRINT '表数据已清空';
-- GO

-- 插入特征数据
-- 检查是否已有数据，如果没有则插入
IF NOT EXISTS (SELECT 1 FROM Features)
BEGIN
    INSERT INTO [dbo].[Features] ([name]) VALUES (N'有毛发');
    INSERT INTO [dbo].[Features] ([name]) VALUES (N'哺乳');
    INSERT INTO [dbo].[Features] ([name]) VALUES (N'有羽毛');
    INSERT INTO [dbo].[Features] ([name]) VALUES (N'会下蛋');
    INSERT INTO [dbo].[Features] ([name]) VALUES (N'有翅膀');
    INSERT INTO [dbo].[Features] ([name]) VALUES (N'吃肉');
    INSERT INTO [dbo].[Features] ([name]) VALUES (N'有蹄');
    INSERT INTO [dbo].[Features] ([name]) VALUES (N'反刍');
    INSERT INTO [dbo].[Features] ([name]) VALUES (N'黄褐色');
    INSERT INTO [dbo].[Features] ([name]) VALUES (N'有暗斑点');
    INSERT INTO [dbo].[Features] ([name]) VALUES (N'有黑色条纹');
    INSERT INTO [dbo].[Features] ([name]) VALUES (N'有长脖子');
    INSERT INTO [dbo].[Features] ([name]) VALUES (N'有长腿');
    INSERT INTO [dbo].[Features] ([name]) VALUES (N'暗斑点');
    INSERT INTO [dbo].[Features] ([name]) VALUES (N'不会飞');
    INSERT INTO [dbo].[Features] ([name]) VALUES (N'黑白两色');
    INSERT INTO [dbo].[Features] ([name]) VALUES (N'会游泳');
    INSERT INTO [dbo].[Features] ([name]) VALUES (N'有鳍');
    INSERT INTO [dbo].[Features] ([name]) VALUES (N'有长鼻子');
    INSERT INTO [dbo].[Features] ([name]) VALUES (N'有鳞片');
    INSERT INTO [dbo].[Features] ([name]) VALUES (N'冷血');
    INSERT INTO [dbo].[Features] ([name]) VALUES (N'没有腿');
    INSERT INTO [dbo].[Features] ([name]) VALUES (N'会爬行');
    INSERT INTO [dbo].[Features] ([name]) VALUES (N'有钩状喙');
    INSERT INTO [dbo].[Features] ([name]) VALUES (N'有爪');
    INSERT INTO [dbo].[Features] ([name]) VALUES (N'有长翅膀');
    INSERT INTO [dbo].[Features] ([name]) VALUES (N'有管状鼻孔');
    INSERT INTO [dbo].[Features] ([name]) VALUES (N'棕色');
    INSERT INTO [dbo].[Features] ([name]) VALUES (N'有犬齿');
    INSERT INTO [dbo].[Features] ([name]) VALUES (N'眼盯前方');
    INSERT INTO [dbo].[Features] ([name]) VALUES (N'穴居');
    INSERT INTO [dbo].[Features] ([name]) VALUES (N'小体型');
    INSERT INTO [dbo].[Features] ([name]) VALUES (N'冬眠');
    INSERT INTO [dbo].[Features] ([name]) VALUES (N'善飞');
    INSERT INTO [dbo].[Features] ([name]) VALUES (N'有尖嘴');
    INSERT INTO [dbo].[Features] ([name]) VALUES (N'有袋');
    INSERT INTO [dbo].[Features] ([name]) VALUES (N'在澳洲');
    PRINT '特征数据插入成功';
END
ELSE
BEGIN
    PRINT '特征表已有数据，跳过插入';
END
GO

-- 插入规则数据
-- 检查是否已有数据，如果没有则插入
IF NOT EXISTS (SELECT 1 FROM Rules)
BEGIN
    -- 基础分类规则
    INSERT INTO [dbo].[Rules] ([name], [category], [conditions], [conclusion]) 
    VALUES (N'规则1', N'基础分类规则', N'有毛发', N'是哺乳动物');
    
    INSERT INTO [dbo].[Rules] ([name], [category], [conditions], [conclusion]) 
    VALUES (N'规则2', N'基础分类规则', N'哺乳', N'是哺乳动物');
    
    INSERT INTO [dbo].[Rules] ([name], [category], [conditions], [conclusion]) 
    VALUES (N'规则3', N'基础分类规则', N'有羽毛', N'是鸟类');
    
    INSERT INTO [dbo].[Rules] ([name], [category], [conditions], [conclusion]) 
    VALUES (N'规则4', N'基础分类规则', N'会下蛋,有翅膀', N'是鸟类');
    
    INSERT INTO [dbo].[Rules] ([name], [category], [conditions], [conclusion]) 
    VALUES (N'规则5', N'基础分类规则', N'有鳞片,冷血', N'是爬行动物');
    
    -- 哺乳动物细分规则
    INSERT INTO [dbo].[Rules] ([name], [category], [conditions], [conclusion]) 
    VALUES (N'规则6', N'哺乳动物细分规则', N'是哺乳动物,吃肉', N'是食肉动物');
    
    INSERT INTO [dbo].[Rules] ([name], [category], [conditions], [conclusion]) 
    VALUES (N'规则7', N'哺乳动物细分规则', N'是哺乳动物,有犬齿,有爪,眼盯前方', N'是食肉动物');
    
    INSERT INTO [dbo].[Rules] ([name], [category], [conditions], [conclusion]) 
    VALUES (N'规则8', N'哺乳动物细分规则', N'是哺乳动物,有蹄', N'是有蹄类动物');
    
    INSERT INTO [dbo].[Rules] ([name], [category], [conditions], [conclusion]) 
    VALUES (N'规则9', N'哺乳动物细分规则', N'是哺乳动物,反刍', N'是偶蹄类动物');
    
    INSERT INTO [dbo].[Rules] ([name], [category], [conditions], [conclusion]) 
    VALUES (N'规则10', N'哺乳动物细分规则', N'是哺乳动物,有袋', N'是有袋动物');
    
    -- 鸟类细分规则
    INSERT INTO [dbo].[Rules] ([name], [category], [conditions], [conclusion]) 
    VALUES (N'规则11', N'鸟类细分规则', N'是鸟类,不会飞,有长脖子,有长腿,黑色,会游泳', N'结论:是鸵鸟');
    
    INSERT INTO [dbo].[Rules] ([name], [category], [conditions], [conclusion]) 
    VALUES (N'规则12', N'鸟类细分规则', N'是鸟类,不会飞,会游泳,黑色,白色', N'结论:是企鹅');
    
    INSERT INTO [dbo].[Rules] ([name], [category], [conditions], [conclusion]) 
    VALUES (N'规则13', N'鸟类细分规则', N'是鸟类,会飞,吃肉,黄褐色,有钩状喙,有爪', N'结论:是鹰');
    
    INSERT INTO [dbo].[Rules] ([name], [category], [conditions], [conclusion]) 
    VALUES (N'规则14', N'鸟类细分规则', N'是鸟类,会飞,有长翅膀,有管状鼻孔', N'结论:是信天翁');
    
    INSERT INTO [dbo].[Rules] ([name], [category], [conditions], [conclusion]) 
    VALUES (N'规则15', N'鸟类细分规则', N'是鸟类,善飞,有尖嘴,小体型', N'结论:是燕子');
    
    -- 爬行动物细分规则
    INSERT INTO [dbo].[Rules] ([name], [category], [conditions], [conclusion]) 
    VALUES (N'规则16', N'爬行动物细分规则', N'是爬行动物,没有腿,会爬行', N'结论:是蛇');
    
    -- 有袋动物细分规则
    INSERT INTO [dbo].[Rules] ([name], [category], [conditions], [conclusion]) 
    VALUES (N'规则17', N'有袋动物细分规则', N'是有袋动物,在澳洲', N'结论:是袋鼠');
    
    -- 特定动物识别规则
    INSERT INTO [dbo].[Rules] ([name], [category], [conditions], [conclusion]) 
    VALUES (N'规则18', N'特定动物识别规则', N'是哺乳动物,是食肉动物,黄褐色,有暗斑点', N'结论:是猎豹');
    
    INSERT INTO [dbo].[Rules] ([name], [category], [conditions], [conclusion]) 
    VALUES (N'规则19', N'特定动物识别规则', N'是哺乳动物,是食肉动物,黄褐色,有黑色条纹', N'结论:是老虎');
    
    INSERT INTO [dbo].[Rules] ([name], [category], [conditions], [conclusion]) 
    VALUES (N'规则20', N'特定动物识别规则', N'是有蹄类动物,有长脖子,有长腿,黄褐色,有暗斑点', N'结论:是长颈鹿');
    
    INSERT INTO [dbo].[Rules] ([name], [category], [conditions], [conclusion]) 
    VALUES (N'规则21', N'特定动物识别规则', N'是有蹄类动物,白色,有黑色条纹', N'结论:是斑马');
    
    INSERT INTO [dbo].[Rules] ([name], [category], [conditions], [conclusion]) 
    VALUES (N'规则22', N'特定动物识别规则', N'是哺乳动物,会游泳,是食肉动物,棕色,有鳍,有长鼻子', N'结论:是海豚');
    
    INSERT INTO [dbo].[Rules] ([name], [category], [conditions], [conclusion]) 
    VALUES (N'规则23', N'特定动物识别规则', N'是哺乳动物,穴居,小体型,冬眠', N'结论:是熊');
    
    PRINT '规则数据插入成功';
END
ELSE
BEGIN
    PRINT '规则表已有数据，跳过插入';
END
GO

-- 查询数据，验证导入结果
PRINT '=== 特征数据验证 ===';
SELECT COUNT(*) AS 特征数量 FROM Features;
PRINT '前10条特征数据:';
SELECT TOP 10 * FROM Features ORDER BY id;
GO

PRINT '=== 规则数据验证 ===';
SELECT COUNT(*) AS 规则数量 FROM Rules;
PRINT '前10条规则数据:';
SELECT TOP 10 * FROM Rules ORDER BY id;
GO

PRINT '数据库脚本执行完成！';
PRINT '数据库连接信息：';
PRINT '- 服务器名称: (本地)';
PRINT '- 数据库名称: AnimalExpertSystem';
PRINT '- 身份验证: Windows身份验证 或 SQL Server身份验证';
PRINT '如果使用SQL Server身份验证，请确保已创建相应的登录名和用户，并授予适当的权限。';